/**
 * Copyright (c) 2007,中企动力华南研发中心<br>
 * All rights reserved.
 */
package com.ce.pms.renovation.dao.impl;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;

import com.ce.pms.base.dao.impl.PmsBaseDaoImpl;
import com.ce.pms.common.util.SqlConfig;
import com.ce.pms.renovation.dao.IDepositAccountingDao;
import com.ce.pms.renovation.model.DepositAccountingDO;
import com.ce.pms.renovation.vo.DepositAccountingQueryVO;
import com.ce.pms.renovation.vo.DepositAccountingListVO;
import com.ce.util.BlankUtil;
import com.sitechasia.webx.core.support.Page;

/**
 * 功能描述：保证金<br>
 * @author 罗军林 创建日期：2008-12-30
 */
public class DepositAccountingDaoImpl extends PmsBaseDaoImpl<DepositAccountingDO> implements IDepositAccountingDao {
	private SqlConfig sqlConfig=new SqlConfig("RenovateSQL.xml");
	private final String QUERY_DEPOSIT_ACCOUNTINGS="queryDepositAccountings";

	public Page queryDepositAccountings(DepositAccountingQueryVO queryCondition) {
		Map<String, Object> parameter=new HashMap<String, Object>();
		String sql=sqlConfig.getSql(QUERY_DEPOSIT_ACCOUNTINGS);
		sql=sql.replaceAll("_queryCondition", buildCondition(parameter, queryCondition));
		sql=sql.replaceAll("_orderStr", buildOrder(queryCondition));

		Page page= this.pagedSqlNameQueryByPageNo(sql, queryCondition.getPageNo(), queryCondition.getPageSize(), parameter);
		page.setResult(assembleDepositO2V(page.getResult()));
		return page;
	}

	/**
	 * 功能描述:查询指定许可证的核算结果<br>
	 * @param licenceId
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-12-30<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	public DepositAccountingDO getDepositAccountings(Long licenceId){
		String hql="select deposit FROM DepositAccountingDO deposit where deposit.inspectionRequisitionDO.renovateLicence.id=?";
		Query query=createQuery(hql, licenceId);
		return (DepositAccountingDO)query.uniqueResult();
	}

	/**
	 * 功能描述:将原生SQL查询的申请对象数组转成申请VO<br>
	 * @param result
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-12-23<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	private List<DepositAccountingListVO> assembleDepositO2V(List<Object[]> result) {
		List<DepositAccountingListVO> temp=new ArrayList<DepositAccountingListVO>();
		for(Object[] item : result){
			DepositAccountingListVO depositAccount=new DepositAccountingListVO();
			depositAccount.setLicenceNumber( (String)item[0] );//许可证号
			depositAccount.setHouseNumber( (String)item[1] );//房号
			depositAccount.setClientNames( (String)item[2] );//业主姓名
			depositAccount.setReturnTerm( (Date)item[3] );//退还期限
			depositAccount.setAccountingDate( (Date)item[4] );//退还期限
			depositAccount.setAccountingMan( (String)item[5] );//核算人
			depositAccount.setApprovalState( (String)item[6] );//审批状态
			depositAccount.setReturnSum( (BigDecimal)item[7] );//退还金额
			depositAccount.setFeeState( (String)item[8] );//费用状态
			depositAccount.setInspectionRequistionId( ((BigInteger)item[9]).longValue() );//验收申请ID
			depositAccount.setLicenceId( ((BigInteger)item[10]).longValue() );//许可证ID
			depositAccount.setDepositAccountingId( item[11]==null ? null : ((BigInteger)item[11]).longValue() );//核算记录ID
			temp.add(depositAccount);
		}
		return temp;
	}

	/**
	 * 功能描述:构建查询条件<br>
	 * @param parameter
	 * @param queryCondition
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-12-23<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	private String buildCondition(final Map<String, Object> parameter,final DepositAccountingQueryVO queryCondition){
		StringBuilder str=new StringBuilder();
		//楼栋
		if(queryCondition.getBuildingId()!=null){
			str.append(" and HB.BUILDING_ID=:buildingId ");
			parameter.put("buildingId", queryCondition.getBuildingId());
		}

		//房号
		if(!BlankUtil.isBlank(queryCondition.getHouseNumber())){
			str.append(" and HM.HOUSE_NUMBER like :houseNumber ");
			parameter.put("houseNumber", "%" + queryCondition.getHouseNumber().trim()+"%" );
		}

		//审批状态
		if(!BlankUtil.isBlank(queryCondition.getApprovalState())){
			str.append(" and DEP.APPROVAL_STATE=:approvalState ");
			parameter.put("approvalState", queryCondition.getApprovalState().trim());
		}

		//客户姓名
		if(!BlankUtil.isBlank(queryCondition.getClientNames())){
			str.append(" and REQ.CLIENT_NAMES like :clientNames ");
			parameter.put("clientNames", "%"+queryCondition.getClientNames().trim()+"%");
		}

		//许可证号
		if(!BlankUtil.isBlank(queryCondition.getLicenceNumber())){
			str.append(" and LIC.LICENCE_NUMBER like :licenceNumber ");
			parameter.put("licenceNumber", "%"+queryCondition.getLicenceNumber().trim()+"%");
		}

		//核算人
		if(!BlankUtil.isBlank(queryCondition.getAccountingName())){
			str.append(" and EMP.EMPLOYEE_NAME like :accountingName ");
			parameter.put("accountingName", "%"+queryCondition.getAccountingName().trim()+"%");
		}

		//费用状态
		if(!BlankUtil.isBlank(queryCondition.getFeeState())){
			str.append(" and DEP.HANDLE_YN=:feeState ");
			parameter.put("feeState", queryCondition.getFeeState());
		}


		//核算开始时间
		if(null != queryCondition.getAccountingBegin()){
			str.append(" and DEP.SYS_DATE >=:accountingBegin ");
			parameter.put("accountingBegin", queryCondition.getAccountingBegin());
		}

		//核算结束时间
		if(null != queryCondition.getAccountingEnd()){
			str.append(" and DEP.SYS_DATE < ADDDATE(:accountingEnd,1) ");
			parameter.put("accountingEnd", queryCondition.getAccountingEnd());
		}

		//退还期限以天数来查询
		if(null != queryCondition.getReturnTerm()){
			str.append(" and (INS.RETURN_FEE_DATE >= CURDATE() and INS.RETURN_FEE_DATE < ADDDATE(CURDATE(),:returTerm)) ");
			parameter.put("returTerm", queryCondition.getReturnTerm());
		}

		//退还期限开始时间
		if(null != queryCondition.getReturnBegin()){
			str.append(" and INS.RETURN_FEE_DATE >=:returnBegin ");
			parameter.put("returnBegin", queryCondition.getReturnBegin());
		}

		//退还期限结束时间
		if(null != queryCondition.getReturnEnd()){
			str.append(" and INS.RETURN_FEE_DATE < ADDDATE(:returnEnd,1) ");
			parameter.put("returnEnd", queryCondition.getReturnEnd());
		}

		return str.toString();
	}

	/**
	 * 功能描述:<br>
	 * @param orderStr
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-12-23<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	private String buildOrder(DepositAccountingQueryVO queryCondition){
		if(null==queryCondition.getOrderValue()){
			return " ORDER BY DEP.SYS_DATE DESC ";
		}else if(queryCondition.getOrderValue().equalsIgnoreCase("houseNumber")
				&&  queryCondition.getOrderKey().equalsIgnoreCase("asc")){
			return " ORDER BY HB.SEQ_NO ASC,FL.STOREY_NUMBER ASC,HM.HOUSE_NUMBER ASC";
		}else if(queryCondition.getOrderValue().equalsIgnoreCase("houseNumber")
				&&  queryCondition.getOrderKey().equalsIgnoreCase("desc")){
			return " ORDER BY HB.SEQ_NO DESC,FL.STOREY_NUMBER DESC,HM.HOUSE_NUMBER DESC";
		}else if(queryCondition.getOrderValue().equalsIgnoreCase("returnTerm")){
			return " ORDER BY INS.RETURN_FEE_DATE "+queryCondition.getOrderKey();
		}else if(queryCondition.getOrderValue().equalsIgnoreCase("accountingDate")){
			return " ORDER BY DEP.`SYS_DATE` "+queryCondition.getOrderKey();
		}else{
			return " ORDER BY DEP.SYS_DATE DESC ";
		}
	}

}
